Load packages
library(tidyverse)
library(lubridate)
library(hms)
Attaching package: ‘hms’
The following object is masked from ‘package:lubridate’:
hms
library(googlesheets4)
#gs4_deauth()
#ppl_15mins <- read_sheet("https://docs.google.com/spreadsheets/d/1uv5SBYklQ-bArCnWPVG1hOvKu3fpgD1sgU8bTZ8sR5o/edit?usp=sharing")
#add column types
col_datatypes <- c('numeric','numeric','date','text',rep('numeric',99))
#?read_excel
hourly1 <- read_excel("Hourly Usage 220326 to 220624.xlsx", col_types = col_datatypes)
Warning: Expecting numeric in A277 / R277C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'
hourly2 <- read_excel("Hourly Usage 220625 to 230623.xlsx", col_types = col_datatypes)
Warning: Expecting numeric in A1096 / R1096C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'
hourly3 <- read_excel("Hourly Usage 230624 to 231121.xlsx", col_types = col_datatypes)
Warning: Expecting numeric in A457 / R457C1: got 'The information contained in this file is intended for the confidential use by the customer and third parties authorized by the customer to receive the information. Any unauthorized use is prohibited.'
hourly1
hourly2
hourly3
(ppl_15mins <- bind_rows(hourly1,list(hourly2,hourly3)))
NA
NA
hourly_ppl_pivot <- ppl_15mins %>%
rename(date = Date) %>%
pivot_longer(!c("Account Number", "Meter Number", date, "Read Type", Min, Max, Total), names_to = "time", values_to = "kWh")
#rm(hourly_pivot)
hourly_ppl_pivot <- hourly_ppl_pivot %>%
mutate(time = parse_time(time, '%H:%M %p'), month = month(date, label=TRUE), year = year(date), yday = yday(date), wday = wday(date, label=TRUE))
(hourly_ppl_net <- hourly_ppl_pivot %>%
filter(`Read Type` == "kWh Net"))
Import solar production from another notebook
ggplot(hourly_production, aes(datetime, energy_produced_Wh)) +
geom_point()

ggplot(hourly_production, aes(time, energy_produced_Wh)) +
theme(axis.text.x = element_text(angle = 90)) +
geom_point()

ggplot(hourly_production, aes(datetime, energy_produced_Wh)) +
geom_point()

ggplot(hourly_production, aes(time, energy_produced_Wh)) +
theme(axis.text.x = element_text(angle = 90)) +
geom_point()

Net + Produced = Consumedggplot(hourly_production, aes(datetime,
energy_produced_Wh)) +
geom_point()
ggplot(hourly_production, aes(time, energy_produced_Wh)) +
theme(axis.text.x = element_text(angle = 90)) +
geom_point()
hourly_ppl_net %>% arrange(desc(date))
hourly_production %>% arrange(desc(date))
ggplot(hourly_electricity, aes(x=time)) +
geom_point(aes(y=consumed_kWh,color="red"))

ggplot(hourly_electricity, aes(x=datetime)) +
geom_point(aes(y=consumed_kWh,color="red"))

Bring in Enphase energy production
Bring in PPL net energy usage
Calculate consumption
LS0tCnRpdGxlOiAiSG91cmx5IEVsZWN0cmljaXR5IENvbnN1bXB0aW9uIgpvdXRwdXQ6IGh0bWxfbm90ZWJvb2sKLS0tCgpMb2FkIHBhY2thZ2VzCmBgYHtyfQpsaWJyYXJ5KHRpZHl2ZXJzZSkKbGlicmFyeShsdWJyaWRhdGUpCmxpYnJhcnkoaG1zKQpsaWJyYXJ5KGdvb2dsZXNoZWV0czQpCmBgYAoKYGBge3J9CgojZ3M0X2RlYXV0aCgpCiNwcGxfMTVtaW5zIDwtIHJlYWRfc2hlZXQoImh0dHBzOi8vZG9jcy5nb29nbGUuY29tL3NwcmVhZHNoZWV0cy9kLzF1djVTQllrbFEtYkFyQ25XUFZHMWhPdkt1M2ZwZ0Qxc2dVOGJUWjhzUjVvL2VkaXQ/dXNwPXNoYXJpbmciKQojYWRkIGNvbHVtbiB0eXBlcwoKCmNvbF9kYXRhdHlwZXMgPC0gYygnbnVtZXJpYycsJ251bWVyaWMnLCdkYXRlJywndGV4dCcscmVwKCdudW1lcmljJyw5OSkpCgojP3JlYWRfZXhjZWwKCmhvdXJseTEgPC0gcmVhZF9leGNlbCgiSG91cmx5IFVzYWdlIDIyMDMyNiB0byAyMjA2MjQueGxzeCIsIGNvbF90eXBlcyA9IGNvbF9kYXRhdHlwZXMpCmhvdXJseTIgPC0gcmVhZF9leGNlbCgiSG91cmx5IFVzYWdlIDIyMDYyNSB0byAyMzA2MjMueGxzeCIsIGNvbF90eXBlcyA9IGNvbF9kYXRhdHlwZXMpCmhvdXJseTMgPC0gcmVhZF9leGNlbCgiSG91cmx5IFVzYWdlIDIzMDYyNCB0byAyMzExMjEueGxzeCIsIGNvbF90eXBlcyA9IGNvbF9kYXRhdHlwZXMpCgpob3VybHkxCmhvdXJseTIKaG91cmx5MwoKKHBwbF8xNW1pbnMgPC0gYmluZF9yb3dzKGhvdXJseTEsbGlzdChob3VybHkyLGhvdXJseTMpKSkKCgpgYGAKCmBgYHtyfQpob3VybHlfcHBsX3Bpdm90IDwtIHBwbF8xNW1pbnMgJT4lIAogIHJlbmFtZShkYXRlID0gRGF0ZSkgJT4lIAogIHBpdm90X2xvbmdlcighYygiQWNjb3VudCBOdW1iZXIiLCAiTWV0ZXIgTnVtYmVyIiwgZGF0ZSwgIlJlYWQgVHlwZSIsIE1pbiwgTWF4LCBUb3RhbCksIG5hbWVzX3RvID0gInRpbWUiLCB2YWx1ZXNfdG8gPSAia1doIikgCgojcm0oaG91cmx5X3Bpdm90KQoKaG91cmx5X3BwbF9waXZvdCA8LSBob3VybHlfcHBsX3Bpdm90ICU+JSAKICBtdXRhdGUodGltZSA9IHBhcnNlX3RpbWUodGltZSwgJyVIOiVNICVwJyksIG1vbnRoID0gbW9udGgoZGF0ZSwgbGFiZWw9VFJVRSksIHllYXIgPSB5ZWFyKGRhdGUpLCB5ZGF5ID0geWRheShkYXRlKSwgd2RheSA9IHdkYXkoZGF0ZSwgbGFiZWw9VFJVRSkpCgooaG91cmx5X3BwbF9uZXQgPC0gaG91cmx5X3BwbF9waXZvdCAlPiUgCiAgZmlsdGVyKGBSZWFkIFR5cGVgID09ICJrV2ggTmV0IikpCmBgYAoKSW1wb3J0IHNvbGFyIHByb2R1Y3Rpb24gZnJvbSBhbm90aGVyIG5vdGVib29rCgpgYGB7cn0KZ2dwbG90KGhvdXJseV9wcm9kdWN0aW9uLCBhZXMoZGF0ZXRpbWUsIGVuZXJneV9wcm9kdWNlZF9XaCkpICsKICBnZW9tX3BvaW50KCkKCmdncGxvdChob3VybHlfcHJvZHVjdGlvbiwgYWVzKHRpbWUsIGVuZXJneV9wcm9kdWNlZF9XaCkpICsKICB0aGVtZShheGlzLnRleHQueCA9IGVsZW1lbnRfdGV4dChhbmdsZSA9IDkwKSkgKwogIGdlb21fcG9pbnQoKQpgYGAKCmBgYHtyfQpnZ3Bsb3QoaG91cmx5X3Byb2R1Y3Rpb24sIGFlcyhkYXRldGltZSwgZW5lcmd5X3Byb2R1Y2VkX1doKSkgKwogIGdlb21fcG9pbnQoKQoKZ2dwbG90KGhvdXJseV9wcm9kdWN0aW9uLCBhZXModGltZSwgZW5lcmd5X3Byb2R1Y2VkX1doKSkgKwogIHRoZW1lKGF4aXMudGV4dC54ID0gZWxlbWVudF90ZXh0KGFuZ2xlID0gOTApKSArCiAgZ2VvbV9wb2ludCgpCmBgYAoKTmV0ICsgUHJvZHVjZWQgPSBDb25zdW1lZGdncGxvdChob3VybHlfcHJvZHVjdGlvbiwgYWVzKGRhdGV0aW1lLCBlbmVyZ3lfcHJvZHVjZWRfV2gpKSArCgogIGdlb21fcG9pbnQoKQoKZ2dwbG90KGhvdXJseV9wcm9kdWN0aW9uLCBhZXModGltZSwgZW5lcmd5X3Byb2R1Y2VkX1doKSkgKwoKICB0aGVtZShheGlzLnRleHQueCA9IGVsZW1lbnRfdGV4dChhbmdsZSA9IDkwKSkgKwoKICBnZW9tX3BvaW50KCkKYGBge3J9CgojIGhvdXJseV9wcGxfbmV0IDwtIGhvdXJseV9wcGxfbmV0ICU+JSBtdXRhdGUoZGF0ZSA9IGFzX2RhdGUoZGF0ZSkpCgojIGhvdXJseV9wcGxfbmV0ICU+JSBhcnJhbmdlKGRlc2MoZGF0ZSkpCiMgaG91cmx5X3Byb2R1Y3Rpb24gJT4lIGFycmFuZ2UoZGVzYyhkYXRlKSkKCgooaG91cmx5X2VsZWN0cmljaXR5IDwtIGhvdXJseV9wcGxfbmV0ICU+JSAKICAgIGlubmVyX2pvaW4oaG91cmx5X3Byb2R1Y3Rpb24sIGJ5ID0gam9pbl9ieShkYXRlLHRpbWUpKSAgJT4lIAogICAgbXV0YXRlKGNvbnN1bWVkX2tXaCA9IGtXaCArIGVuZXJneV9wcm9kdWNlZF9XaC8xMDAwLCBwcm9kdWNlZF9rV2ggPSBlbmVyZ3lfcHJvZHVjZWRfV2gvMTAwMCkgICU+JSAKICAgIHJlbmFtZShuZXRfa1doID0ga1doKSAlPiUgCiAgICBzZWxlY3QoZGF0ZXRpbWUsIGRhdGUsIHRpbWUsIG5ldF9rV2gsIHByb2R1Y2VkX2tXaCwgY29uc3VtZWRfa1doKSkKCmBgYAoKYGBge3J9CmdncGxvdChob3VybHlfZWxlY3RyaWNpdHksIGFlcyh4PXRpbWUpKSArCiAgZ2VvbV9wb2ludChhZXMoeT1jb25zdW1lZF9rV2gsY29sb3I9InJlZCIpKSAKCmdncGxvdChob3VybHlfZWxlY3RyaWNpdHksIGFlcyh4PWRhdGV0aW1lKSkgKwogIGdlb21fcG9pbnQoYWVzKHk9Y29uc3VtZWRfa1doLGNvbG9yPSJyZWQiKSkgCmBgYAoKCgoKQnJpbmcgaW4gRW5waGFzZSBlbmVyZ3kgcHJvZHVjdGlvbgpgYGB7cn0KCmBgYAoKQnJpbmcgaW4gUFBMIG5ldCBlbmVyZ3kgdXNhZ2UKYGBge3J9CgpgYGAKCkNhbGN1bGF0ZSBjb25zdW1wdGlvbgpgYGB7cn0KCmBgYAoKCgo=